#coding=utf-8
import MySQLdb,sys
reload(sys)
sys.setdefaultencoding('utf-8')

class session(object):
    def __init__(self,ip='192.168.23.100',user='root',passwd='palmgo',db=''):
        self.conn= MySQLdb.connect(
        host=ip,
        port = 3306,
        user=user,
        passwd=passwd,
        db =db,
        charset='utf8',
        )
    def data_output(self,sql):  ##通用方法，返回数据库查询结果
        try:
            cur = self.conn.cursor()
            #获得表中有多少条数据
            aa=cur.execute(sql)
            #打印表中的多少数据
            info = cur.fetchall()
            cur.close()
            self.conn.commit()
            # self.conn.close()
            return info
        except Exception as e:
            print str(e)
            return str(e)


    def data(self,begin,end,kehu):
        try:
            self.cur = self.conn.cursor()
            #获得表中有多少条数据
            self.aa=self.cur.execute("SELECT substr(ACCESS_TIME,1,10) FROM PUB_PARTNER_LOG t WHERE ACCESS_TIME >= %d and ACCESS_TIME < %d and  \
            PARTNER_LOGIN = '%s'"  %(begin,end,kehu))

            #打印表中的多少数据
            self.info = self.cur.fetchall()
            self.cur.close()
            self.conn.commit()
            self.conn.close()
            return self.info
        except Exception as e:
            print str(e)
            return str(e)
    def city_data(self,begin,end,kehu):
        try:
            self.cur = self.conn.cursor()
            #获得表中有多少条数据
            self.aa=self.cur.execute("SELECT CITY_CODE,substr(ACCESS_TIME,1,10) FROM PUB_PARTNER_LOG t WHERE ACCESS_TIME >= %d and ACCESS_TIME < %d and  \
            PARTNER_LOGIN = '%s'"  %(begin,end,kehu))
            #打印表中的多少数据
            self.info = self.cur.fetchall()
            self.cur.close()
            self.conn.commit()
            self.conn.close()
            return self.info
        except Exception as e:
            print str(e)
            return str(e)

    def all_data(self,begin,end,kehu):
        try:
            self.cur = self.conn.cursor()
            #获得表中有多少条数据
            self.aa=self.cur.execute("SELECT partner_login,substr(ACCESS_TIME,1,10),city_code,business_name,operation_num,data_file_name,from_ip FROM \
PUB_PARTNER_LOG t WHERE ACCESS_TIME >= %d and ACCESS_TIME < %d and  PARTNER_LOGIN = '%s' ORDER by ACCESS_TIME limit 0,20000"  %(begin,end,kehu))
            #打印表中的多少数据
            self.info = self.cur.fetchall()
            self.cur.close()
            self.conn.commit()
            self.conn.close()
            return self.info
        except Exception as e:
            print str(e)
            return str(e)

    def event_inter_data(self):
        try:
            self.cur = self.conn.cursor()
            #获得表中有多少条数据
            self.aa=self.cur.execute("select LocationText,DATE_FORMAT(StartTime,'%Y-%m-%d %T'),DATE_FORMAT(EndTime,'%Y-%m-%d %T'),Remark,StartName \
            from trafficinfotab_intercity where UNIX_TIMESTAMP(date_format(EndTime,'%Y-%m-%d %T')) >= UNIX_TIMESTAMP(date_format(now(),'%Y-%m-%d %T'))" )
            #打印表中的多少数据
            self.info = self.cur.fetchall()
            self.cur.close()
            self.conn.commit()
            self.conn.close()
            return self.info
        except Exception as e:
            print str(e)
            return str(e)
